﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Configuration;

namespace HRdex_Final.Announcement
{
    public partial class WebForm2 : System.Web.UI.Page
    {
        DataTable dtbl = new DataTable("dtbl");
        SqlConnection conn = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\HRdexDB.mdf;Integrated Security=True;User Instance=True");
        //string conn = ConfigurationManager.ConnectionStrings["HRmainConnectionString"].ConnectionString;
        string SessionID()
        {

            return Session["EmployeeID"].ToString();
        }
        protected void Page_Load(object sender, EventArgs e)
        {
            try
            {
                SqlConnection conn2 = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\HRdexDB.mdf;Integrated Security=True;User Instance=True");
                SqlCommand cmd = new SqlCommand("SELECT formEmployees.EmployeeID, catUserLevel.catUserLevelName FROM formEmployees INNER JOIN catUserLevel ON formEmployees.UserLevelID = catUserLevel.UserLevelID WHERE formEmployees.EmployeeID = " + SessionID(), conn2);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable("dt");
                da.Fill(dt);
                if (dt.Rows[0].ItemArray[1].ToString() == "Faculty" || dt.Rows[0].ItemArray[1].ToString() == "Employee")
                {
                    btnEdt.Visible = false;
                    btnSubmit.Visible = false;
                }

                lblConfirmation.Visible = false;
                string announcementID = Request.QueryString["announcementID"];
                if (announcementID == null)
                {
                    txtAnnouncementID.Enabled = true;
                    txtDescription.Enabled = true;
                    txtSubject.Enabled = true;
                    fuAttachment.Visible = true;
                    ddlCategories.Enabled = true;
                    btnDownload.Visible = false;

                }
                else
                {
                    Select();
                }
            }
            catch (Exception)
            {
                
                //throw;
            }
            
        }



        protected void txtSubmit_Click(object sender, EventArgs e)
        {
            try
            {
                string announcementID = Request.QueryString["AnnouncementID"];
                //connection Add data
                SqlDataAdapter adapter = new SqlDataAdapter();
                DataSet ds = new DataSet();


                if (txtAnnouncementID.Text != announcementID)
                {
                    conn.Open();
                    //Add 
                    string sqlc;


                    Stream fs = fuAttachment.PostedFile.InputStream;
                    BinaryReader br = new BinaryReader(fs);
                    Byte[] bytes = br.ReadBytes((Int32)fs.Length);

                    string filePath = fuAttachment.PostedFile.FileName;
                    string fileType = Path.GetExtension(filePath);
                    string fileName = Path.GetFileName(filePath);
                    if (filePath != null)
                    {
                        sqlc = "Insert Into formAnnouncement (Subject, Body, CatAnnID, Attachment, DateTime, AttachName, AttachType) " +
                            "Values (@subject, @body, @catannID, @attachment,@datetime, @attachName, @attachType)";
                    }
                    else
                    {
                        sqlc = "Insert Into formAnnouncement (Subject, Body, CatAnnID, DateTime) " +
                           "Values (@subject, @body, @catannID, @datetime)";
                    }

                    SqlCommand cmd = new SqlCommand(sqlc, conn);

                    cmd.Parameters.Add("@subject", SqlDbType.VarChar).Value = txtSubject.Text;
                    cmd.Parameters.Add("@body", SqlDbType.VarChar).Value = txtDescription.Text;
                    cmd.Parameters.Add("@catannID", SqlDbType.Int).Value = ddlCategories.SelectedValue;
                    cmd.Parameters.Add("@datetime", SqlDbType.DateTime).Value = DateTime.Now;


                    if (filePath != null)
                    {
                        cmd.Parameters.Add("@attachment", SqlDbType.Binary).Value = bytes;
                        cmd.Parameters.Add("@attachName", SqlDbType.VarChar).Value = fileName;
                        cmd.Parameters.Add("@attachType", SqlDbType.VarChar).Value = fileType;
                    }

                    cmd.ExecuteNonQuery();

                    conn.Close();
                    fuAttachment.Visible = true;
                    btnDownload.Visible = false;
                    lblConfirmation.Visible = true;
                    lblConfirmation.Text = "You have successfully add new Announcement!";
                }
                else
                {
                    EditHere();
                    //uploadedit();
                }

                txtAnnouncementID.Enabled = false;
                txtDescription.Enabled = false;
                txtSubject.Enabled = false;
                fuAttachment.Visible = false;
                ddlCategories.Enabled = false;
            }
            catch (Exception)
            {
                
                
            }
            
        }

        override protected void OnInit(EventArgs e)
        {
            InitializeComponent();
            base.OnInit(e);
        }

        private void InitializeComponent()
        {
            //this.cmdStore.Click += new System.EventHandler(this.cmdStore_Click);
            this.Load += new System.EventHandler(this.Page_Load);

        }


        public void EditHere()
        {
            try
            {
                string announcementID = Request.QueryString["AnnouncementID"];
                string edit = ("UPDATE [formAnnouncement] SET [Subject] ='" + txtSubject.Text + "', [Body] ='" + txtDescription.Text + "' WHERE  ([AnnouncementID] = " + announcementID + ")");
                SqlCommand cmd2 = new SqlCommand(edit, conn);
                SqlDataAdapter adapt = new SqlDataAdapter(cmd2);
                conn.Open();

            
                cmd2.Parameters.AddWithValue("Subject", txtSubject.Text);
                cmd2.Parameters.AddWithValue("Body", txtDescription.Text);
                //cmd2.Parameters.AddWithValue("CatAnnID", ddlCategories.SelectedValue);

                cmd2.ExecuteNonQuery();
                conn.Close();
                fuAttachment.Visible = true;
                btnDownload.Visible = false;
                lblConfirmation.Visible = true;
                lblConfirmation.Text = "You have successfully Edited the Selected Announcement";
            }
            catch (Exception)
            {
                
                throw;
            }
            
  
        }

        private void uploadedit()
        {
            try
            {
                    string filepath = fuAttachment.PostedFile.FileName;
                    string filename = Path.GetFileName(filepath);
                    //lblDownload.Text = filename;

                    Stream fs = fuAttachment.PostedFile.InputStream;
                    BinaryReader br = new BinaryReader(fs);
                    Byte[] bytes = br.ReadBytes((Int32)fs.Length);

                    string filePath = fuAttachment.PostedFile.FileName;
                    string fileType = Path.GetExtension(filePath);
                    string fileName = Path.GetFileName(filePath);

                    string announcementID = Request.QueryString["AnnouncementID"];
                    string edit = ("UPDATE formAnnouncement SET  Attachment = @Data , AttachName = "+ fileName + " , AttachType = " + fileType + " WHERE  ([AnnouncementID] = " + announcementID + ")");
                    SqlCommand cmd = new SqlCommand(edit, conn);
                    cmd.Parameters.Add("@Data", SqlDbType.Binary).Value = bytes;
                    SqlDataAdapter adapt = new SqlDataAdapter();

                    conn.Open();

                    cmd.ExecuteNonQuery();
                    conn.Close();
            }
            catch (Exception)
            {
                
                //throw;
            }
            


        }

        protected void btnDownload_Click(object sender, EventArgs e)
        {
            string announcementID = Request.QueryString["AnnouncementID"];
            string strQuery = "SELECT Attachment, AttachName, AttachType FROM formAnnouncement WHERE AnnouncementID = " + announcementID;
            SqlCommand cmd = new SqlCommand(strQuery);
            DataTable dt = GetData(cmd);
            if (dt != null)
            {
                download(dt);
            }

        }
        private DataTable GetData(SqlCommand cmd)
        {
            DataTable dt = new DataTable();
            SqlDataAdapter sda = new SqlDataAdapter();
            cmd.CommandType = CommandType.Text;
            cmd.Connection = conn;
            try
            {
                conn.Open();
                sda.SelectCommand = cmd;
                sda.Fill(dt);
                return dt;

            }
            catch
            {
                return null;
            }
            finally
            {
                conn.Close();
                sda.Dispose();
                conn.Dispose();
            }

        }
        private void download(DataTable dt)
        {
 
            Byte[] bytes = (Byte[])dt.Rows[0]["Attachment"];
            Response.Buffer = true;
            Response.Charset = "";
            Response.Cache.SetCacheability(HttpCacheability.NoCache);
            Response.ContentType = dt.Rows[0]["AttachType"].ToString();
            Response.AddHeader("content-disposition", "attachment;FileName="
            + dt.Rows[0]["AttachName"].ToString());
            Response.BinaryWrite(bytes);
            Response.Flush();
            Response.End();
        }

        public void Select()
        {
            try
            {
                string announcementID = Request.QueryString["AnnouncementID"];

                if (txtAnnouncementID.Text != announcementID)
                {
                    SqlCommand cmd223 = new SqlCommand("SELECT Subject, Body, DateTime, Attachment, catAnnID FROM formAnnouncement WHERE AnnouncementID = " + announcementID ,conn);
                    SqlDataAdapter da223 = new SqlDataAdapter(cmd223);
                    DataTable dt = new DataTable("dt"); 
                    da223.Fill(dt);
                    txtAnnouncementID.Text = announcementID;
                    //fuAttachment.FileName. = dtbl.Rows[0].ItemArray[3].ToString();
                    txtDescription.Text = dt.Rows[0].ItemArray[1].ToString();
                    lblPubDate.Text = dt.Rows[0].ItemArray[2].ToString();
                    txtSubject.Text = dt.Rows[0].ItemArray[0].ToString();
                    ddlCategories.SelectedValue = dt.Rows[0].ItemArray[4].ToString();

                    //string announcementID = Request.QueryString["AnnouncementID"];
                    string strQuery = "SELECT Attachment, AttachName, AttachType FROM formAnnouncement WHERE AnnouncementID = " + announcementID;
                    SqlCommand cmd = new SqlCommand(strQuery);
                    DataTable dt2 = GetData(cmd);
                    if (dt2 != null)
                    {
                        btnDownload.Visible = true;
                    }
                    else
                    {
                        btnDownload.Visible = false;
                    }


                    txtAnnouncementID.Enabled = false;
                    txtDescription.Enabled = false;
                    txtSubject.Enabled = false;
                    fuAttachment.Visible = false;
                    ddlCategories.Enabled = false;
                    //btnDownload.Visible = true;
                }
            }
            catch (Exception)
            {
                
                throw;
            }


        }

        protected void btnEdit_Click(object sender, EventArgs e)
        {
            txtAnnouncementID.Enabled = true;
            txtDescription.Enabled = true;
            txtSubject.Enabled = true;
            ddlCategories.Enabled = true;
            fuAttachment.Visible = true;
            fuAttachment.Enabled = true;
            btnDownload.Visible = false;
        }

        
    }
}